package org.ntu.csn.as.manager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import num.NameManager;

import org.ntu.csn.as.model.Category;
import org.ntu.csn.as.model.Userinfo;

//database interfaces with database
public class DBManager {

	private static Exception Exception;
	private int id;
	private String question;
	private ArrayList<String> variable;
	private ArrayList<String> relation;
	private ArrayList<String> solution;
	private ArrayList<String> template;
	private ArrayList<String> phrasename;

	/***********************
	 * CONSTRUCTORS *
	 ***********************/
	/**
	 * Fake constructor used to have an empty DBManager (used when adding a new
	 * question)
	 */
	public DBManager() {
		// this.id = -1;
		this.question = "";
		this.variable = new ArrayList<String>();
		this.variable.add("");
		this.relation = new ArrayList<String>();
		this.relation.add("");
		this.solution = new ArrayList<String>();
		this.solution.add("");
		this.phrasename = new ArrayList<String>();
		this.phrasename.add("");
	}

	/** Constructor used to return the list of nearest question */
	public DBManager(int id, String w) {
		this();
		this.id = id;
		this.question = w;
	}

	/** Standar constructor */
	public DBManager(int id, String w, String m, ArrayList<String> def,
			ArrayList<String> rel, ArrayList<String> sol, ArrayList<String> tpl) {
		this();
		this.id = id;
		this.question = w;
		this.variable = def;
		this.relation = rel;
		this.template = tpl;
	}

	/***************************************
	 * MODIFICATION DEFINITION OPERATIONS *
	 **************************************/
	/**
	 * Add a new question to the database
	 * 
	 * @param question
	 *            the new question to create
	 * @param category
	 *            morfology (verb, noun, adjetive...)
	 * @param aVar
	 *            variable(s)
	 * @return id inserted question, if everything go fine -1, otherwise --
	 *         VALIDATION in PRESENTATION TIER -- (JQuery Plugin) Precondition:
	 *         question not null && aVar.length > 0 )
	 */
	public static int addQuestion(String question, String category,
			int diffLevel, String[] aVar, String[] aRel, String[] aSol,
			String[] aTpl) throws NamingException, SQLException {
		Connection co = null;
		PreparedStatement stInsert = null;
		String szSQL;
		int id;
		if (isQuestionExist(question, category, aVar, aRel, aSol)) {
			return -1;
		}
		try {
			co = initConnection();
			stInsert = co
					.prepareStatement("INSERT INTO question(template,category,difflevel) VALUES(?,?,?)");
			stInsert.setString(1, question);
			stInsert.setString(2, category);
            stInsert.setInt(3, diffLevel);
			if (stInsert.executeUpdate() < 1) {
				throw new SQLException("maths.question: " + question
						+ " NOT Inserted");
			}

			/** get the last id (AUTO_INCREMENT) */
			szSQL = "SELECT MAX(id) FROM question";
			ResultSet res = co.createStatement().executeQuery(szSQL);
			if (res.next()) {
				id = res.getInt(1);
			} else {
				// InOut.setStatus(InOut.ERROR_DB,
				// "ERROR: No question.id returned after the INSERT INTO question"
				// + question);
				return -1;
			}

			int numVar = 0;
			stInsert = co
					.prepareStatement("INSERT INTO variable_definition(id, n_var, variable) VALUES(?,?,?)");

			for (String variable : aVar) {
				if (variable.length() > 0) {
					stInsert.setInt(1, id);
					stInsert.setInt(2, numVar);
					stInsert.setString(3, variable);
					if (stInsert.executeUpdate() < 1) {
						// InOut.setStatus(InOut.ERROR_DB,
						// "ERROR: maths.variable_definition: Definition from "
						// + id + " NOT Inserted");
						return -1;
					}
					numVar++;
				}
			}
			int numRel = 0;
			stInsert = co
					.prepareStatement("INSERT INTO variable_relation(id, n_rel, relation) VALUES(?,?,?)");

			for (String relation : aRel) {
				if (relation.length() > 0) {
					stInsert.setInt(1, id);
					stInsert.setInt(2, numRel);
					stInsert.setString(3, relation);
					if (stInsert.executeUpdate() < 1) {
						// InOut.setStatus(InOut.ERROR_DB,
						// "ERROR: maths.variable_definition: Definition from "
						// + id + " NOT Inserted");
						return -1;
					}
					numRel++;
				}
			}
			int numSol = 0;
			stInsert = co
					.prepareStatement("INSERT INTO variable_solution(id, n_sol, solution) VALUES(?,?,?)");

			for (String solution : aSol) {
				if (solution.length() > 0) {
					stInsert.setInt(1, id);
					stInsert.setInt(2, numSol);
					stInsert.setString(3, solution);
					if (stInsert.executeUpdate() < 1) {
						// InOut.setStatus(InOut.ERROR_DB,
						// "ERROR: maths.variable_definition: Definition from "
						// + id + " NOT Inserted");
						return -1;
					}
					numSol++;
				}
			}
			int numTpl = 0;
			stInsert = co
					.prepareStatement("INSERT INTO question_tpl(id, n_tpl, template) VALUES(?,?,?)");

			for (String template : aTpl) {
				if (template.length() > 0) {
					stInsert.setInt(1, id);
					stInsert.setInt(2, numTpl);
					stInsert.setString(3, template);
					if (stInsert.executeUpdate() < 1) {
						// InOut.setStatus(InOut.ERROR_DB,
						// "ERROR: maths.variable_definition: Definition from "
						// + id + " NOT Inserted");
						return -1;
					}
					numTpl++;
				}
			}
		} catch (SQLException ex) {
			// InOut.setStatus(ex.getErrorCode(),
			// "ERROR: There are problems inserting the question<br/>\n" +
			// ex.toString());
			id = -1;
		} finally {
			closeConnection(co, stInsert, null);
		}
		return id;
	}

	public static int addDictionary(String phs, String phses, String type1,
			String type2) throws NamingException, SQLException {
		Connection co = null;
		PreparedStatement stInsert = null;
		String szSQL;
		int id = 0;
		phs = (new NameManager().dbNames(phs));
		if (phses.length() > 2)
			phses = (new NameManager()).upperFirstCase(phses);

		try {
			co = initConnection();
			if (isDictionaryExist(phs, phses, type1, type2)) {
				stInsert = co
						.prepareStatement("UPDATE phrase_dictionary set phrasename=?,phrasesname=?, type1=?, type2=? where phrasename=? or phrasesname=?");
				stInsert.setString(1, phs);
				stInsert.setString(2, phses);
				stInsert.setInt(3, TypNamNum(type1));
				stInsert.setInt(4, TypNamNum(type2));
				stInsert.setString(5, phs);
				if (phses.length() < 1)
					phses = "Dontcare";
				stInsert.setString(6, phses);
			} else {

				stInsert = co
						.prepareStatement("INSERT INTO phrase_dictionary(phrasename,phrasesname, type1, type2,type3,type4) VALUES(?,?,?,?,0,0)");
				stInsert.setString(1, phs);
				stInsert.setString(2, phses);
				stInsert.setInt(3, TypNamNum(type1));
				stInsert.setInt(4, TypNamNum(type2));
			}
			if (stInsert.executeUpdate() < 1) {
				throw new SQLException("new dictionary: " + phs
						+ " NOT Inserted");
			}
		} catch (SQLException ex) {
			// InOut.setStatus(ex.getErrorCode(),
			// "ERROR: There are problems inserting the question<br/>\n" +
			// ex.toString());
			id = -1;
		} finally {
			closeConnection(co, stInsert, null);
		}
		return id;
	}

	public static boolean isUserAndPwdValid(Userinfo userinfo)
			throws NamingException, SQLException {
		Connection co = null;
		PreparedStatement stSelect = null;
		try {
			co = initConnection();
			stSelect = co
					.prepareStatement("SELECT id FROM userinfo WHERE name = ? and pwd = ?");
			stSelect.setString(1, userinfo.getName());
			stSelect.setString(2, userinfo.getPwd());

			ResultSet rs = stSelect.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (SQLException ex) {
			return false;
		} finally {
			closeConnection(co, stSelect, null);
		}
		return false;
	}

	public static boolean isDictionaryExist(String phs, String phses,
			String type1, String type2) throws NamingException, SQLException {
		Connection co = null;
		PreparedStatement stSelect = null;
		String szSQL;
		phs = (new NameManager().dbNames(phs));
		try {
			co = initConnection();
			if (phses == null || phses.length() < 1) {
				stSelect = co
						.prepareStatement("SELECT id FROM phrase_dictionary WHERE phrasename = ?");
				stSelect.setString(1, phs);
			} else {
				stSelect = co
						.prepareStatement("SELECT id FROM phrase_dictionary WHERE phrasename = ? or phrasesname = ? ");
				stSelect.setString(1, phs);
				stSelect.setString(2, phses);
			}

			ResultSet rs = stSelect.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (SQLException ex) {
			// InOut.setStatus(ex.getErrorCode(),
			// "ERROR: There are problems inserting the question<br/>\n" +
			// ex.toString());
			return true;
		} finally {
			closeConnection(co, stSelect, null);
		}
		return false;
	}

	public static boolean isQuestionExist(String question, String category,
			String[] aVar, String[] aRel, String[] aSol)
			throws NamingException, SQLException {
		DBManager result = null;
		Connection co = null;
		PreparedStatement st = null;
		int id;
		String szSQL;

		try {
			szSQL = "SELECT id FROM question WHERE template = ? and category = ?";
			co = initConnection();
			st = co.prepareStatement(szSQL);
			st.setString(1, question);
			st.setString(2, category);
			ResultSet rs = st.executeQuery();
			if (rs.next()) {
				id = rs.getInt("id");
				szSQL = "SELECT n_var, variable FROM variable_definition WHERE id = ?";
				st = co.prepareStatement(szSQL);
				st.setInt(1, id);
				rs = st.executeQuery();

				result = new DBManager();
				result.variable = new ArrayList<String>();
				int i = 0;
				if (rs.last() && rs.getRow() != aVar.length) {
					return false;
				}
				rs = st.executeQuery();
				while (rs.next()) {
					if (rs.getString("variable").equals(aVar[i])) {
						i++;
					} else {
						return false;
					}
				}

				szSQL = "SELECT n_rel, relation FROM variable_relation WHERE id = ?";
				st = co.prepareStatement(szSQL);
				st.setInt(1, id);
				rs = st.executeQuery();
				result.relation = new ArrayList<String>();
				i = 0;
				if (rs.last() && rs.getRow() != aRel.length) {
					return false;
				}
				rs = st.executeQuery();
				while (rs.next()) {
					if (rs.getString("relation").equals(aRel[i])) {
						i++;
					} else {
						return false;
					}
				}

				szSQL = "SELECT n_sol, solution FROM variable_solution WHERE id = ?";
				st = co.prepareStatement(szSQL);
				st.setInt(1, id);
				rs = st.executeQuery();
				result.solution = new ArrayList<String>();
				i = 0;
				if (rs.last() && rs.getRow() != aSol.length) {
					return false;
				}
				rs = st.executeQuery();
				while (rs.next()) {
					if (rs.getString("solution").equals(aSol[i])) {
						i++;
					} else {
						return false;
					}
				}
			} else {
				return false;
			}

		} catch (SQLException ex) {
			// InOut.setStatus(InOut.ERROR_DB,
			// "ERROR: There are a problem updating the question<br/>\n" +
			// ex.toString());
			return true;
		} finally {
			closeConnection(co, st, null);
		}
		return true;
	}

	/**
	 * Modify a question from the database Precondition: question not null &&
	 * aVar.length > 0 )
	 */
	public static int updateQuestion(String id, String question,
			String category, String[] aVar, String[] aRel, String[] aSol,
			String[] aTpl) throws NamingException, SQLException {
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement stUpdate = null;
		int idW;

		try {

			co = initConnection();

			/* Get the Word Identifier */
			try {
				idW = Integer.valueOf(id);
			} catch (NumberFormatException numberFormatException) {
				// InOut.setStatus(InOut.ERROR_PARAM,
				// "ERROR: There are a problem " +
				// "updating the question. The identifier must be an integer<br/>\n");
				return -1;
			}

			/* Update the question values */
			stUpdate = co
					.prepareStatement("UPDATE question SET template=?, category=? WHERE id = ?");
			stUpdate.setString(1, question);
			stUpdate.setString(2, category);
			stUpdate.setInt(3, idW);

			if (stUpdate.executeUpdate() < 1) {
				// InOut.setStatus(InOut.ERROR_DB, "ERROR in '"+
				// stUpdate.toString() +"'");
				return -1;
			}
			/** UPDATE = DELETE old variable(s) + INSERT new one(s) */
			stUpdate = co
					.prepareStatement("DELETE FROM variable_definition WHERE id = ?");
			stUpdate.setInt(1, idW);
			stUpdate.executeUpdate();

			/** UPDATE = DELETE old relation(s) + INSERT new one(s) */
			stUpdate = co
					.prepareStatement("DELETE FROM variable_relation WHERE id = ?");
			stUpdate.setInt(1, idW);
			stUpdate.executeUpdate();

			/** UPDATE = DELETE old solution(s) + INSERT new one(s) */
			stUpdate = co
					.prepareStatement("DELETE FROM variable_solution WHERE id = ?");
			stUpdate.setInt(1, idW);
			stUpdate.executeUpdate();

			/** UPDATE = DELETE old template(s) + INSERT new one(s) */
			stUpdate = co
					.prepareStatement("DELETE FROM question_tpl WHERE id = ?");
			stUpdate.setInt(1, idW);
			stUpdate.executeUpdate();

			int numVar = 0;
			stUpdate = co
					.prepareStatement("INSERT INTO variable_definition(id, n_var, variable) VALUES(?,?,?)");

			for (String variable : aVar) {
				stUpdate.setInt(1, idW);
				stUpdate.setInt(2, numVar);
				stUpdate.setString(3, variable);
				if (stUpdate.executeUpdate() < 1) {
					// InOut.setStatus(InOut.ERROR_DB, "ERROR in '"+
					// stUpdate.toString() +"'");
					return -1;
				}

				numVar++;
			}
			int numRel = 0;
			stUpdate = co
					.prepareStatement("INSERT INTO variable_relation(id, n_rel, relation) VALUES(?,?,?)");

			for (String relation : aRel) {
				stUpdate.setInt(1, idW);
				stUpdate.setInt(2, numRel);
				stUpdate.setString(3, relation);
				if (stUpdate.executeUpdate() < 1) {
					// InOut.setStatus(InOut.ERROR_DB, "ERROR in '"+
					// stUpdate.toString() +"'");
					return -1;
				}

				numRel++;
			}

			int numSol = 0;
			stUpdate = co
					.prepareStatement("INSERT INTO variable_solution(id, n_sol, solution) VALUES(?,?,?)");

			for (String solution : aSol) {
				stUpdate.setInt(1, idW);
				stUpdate.setInt(2, numSol);
				stUpdate.setString(3, solution);
				if (stUpdate.executeUpdate() < 1) {
					// InOut.setStatus(InOut.ERROR_DB, "ERROR in '"+
					// stUpdate.toString() +"'");
					return -1;
				}

				numSol++;
			}
			int numTpl = 0;
			stUpdate = co
					.prepareStatement("INSERT INTO question_tpl(id, n_tpl, template) VALUES(?,?,?)");

			for (String template : aTpl) {
				stUpdate.setInt(1, idW);
				stUpdate.setInt(2, numTpl);
				stUpdate.setString(3, template);
				if (stUpdate.executeUpdate() < 1) {
					// InOut.setStatus(InOut.ERROR_DB, "ERROR in '"+
					// stUpdate.toString() +"'");
					return -1;
				}

				numTpl++;
			}
		} catch (SQLException ex) {
			// InOut.setStatus(InOut.ERROR_DB,
			// "ERROR: There are a problem updating the question<br/>\n" +
			// ex.toString());
			idW = -1;
		} finally {
			closeConnection(co, stUpdate, rs);
		}
		return idW;
	}

	/**
	 * Delete a question from the database
	 * 
	 * @param id
	 *            : identifier of the question
	 * @return id deleted question, if everything go fine -1, otherwise
	 * @throws java.lang.Exception
	 */
	public static int deleteQuestion(String id) throws NamingException,
			SQLException {
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement stDelete = null;
		int idW;
		int[] iRes = { 0, 0, 0 };

		try {
			co = initConnection();

			/* Get the Word Identifier */
			try {
				idW = Integer.valueOf(id);
			} catch (NumberFormatException numberFormatException) {
				throw new IllegalArgumentException("The identifier '" + id
						+ "' must be an integer");
			}

			stDelete = co.prepareStatement("DELETE FROM question WHERE id = ?");
			stDelete.setInt(1, idW);
			iRes[0] = stDelete.executeUpdate();
			for (int aux : iRes) {
				if (aux == 0) {
					// InOut.setStatus(InOut.ERROR_DB,
					// "The question cannot be delete. Posible cause: it doesn't exist.");
					iRes[0] = -1;
					break;
				}
			}
			stDelete = co
					.prepareStatement("DELETE FROM variable_definition WHERE id = ?");
			stDelete.setInt(1, idW);
			iRes[1] = stDelete.executeUpdate();

			stDelete = co
					.prepareStatement("DELETE FROM variable_solution WHERE id = ?");
			stDelete.setInt(1, idW);
			iRes[1] = stDelete.executeUpdate();

			stDelete = co
					.prepareStatement("DELETE FROM question_tpl WHERE id = ?");
			stDelete.setInt(1, idW);
			iRes[1] = stDelete.executeUpdate();

		} catch (SQLException ex) {
			StringBuilder error = new StringBuilder();
			error.append("ERROR: There are a problem deleting the question<br/>\n");
			error.append("SQLState: ").append(ex.getSQLState()).append("<br/>");
			error.append("Message: ").append(ex.getMessage()).append("<br/>");
			error.append("Vendor: ").append(ex.getErrorCode()).append("<br/>");

			// InOut.setStatus(InOut.ERROR_DB, error.toString());
			iRes[0] = -1;
		} finally {
			closeConnection(co, stDelete, rs);
		}

		return iRes[0];
	}

	/**
	 * Generate a random question id (accessing to DB)
	 * 
	 * @return random entry identifier
	 * @throws java.sql.SQLException
	 */
	public int getRandomQuestion(String[] CatListSel) throws SQLException {
		int i;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		String szSQL = "SELECT id FROM question WHERE 1=1";
		if (CatListSel != null) {
			szSQL = "SELECT id FROM question WHERE category in ('"
					+ CatListSel[0] + "'";
			for (i = 1; i < CatListSel.length; i++) {
				szSQL = szSQL + ",'" + CatListSel[i] + "'";
			}
			szSQL = szSQL + ")";
		}

		try {
			co = initConnection();
			st = co.prepareStatement(szSQL);
			rs = st.executeQuery();

			ArrayList<Integer> a = new ArrayList<Integer>();
			while (rs.next()) {
				a.add(rs.getInt("id"));
			}

			Random r = new Random();
			i = a.get(r.nextInt(a.size()));
		} catch (Exception ex) {
			i = 0;
			Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null,
					ex);
		} finally {
			closeConnection(co, st, rs);
		}
		return i;
	}

	/**
	 * Generate a random question id (accessing to DB)
	 * 
	 * @return random entry identifier
	 * @throws java.sql.SQLException
	 */
	public int getRandomTemplate(String id) throws SQLException {
		int i = -1;
		String result = null;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		String szSQL;
		int myid = Integer.valueOf(id);

		try {
			szSQL = "SELECT n_tpl FROM question_tpl WHERE id = ?";

			co = initConnection();
			st = co.prepareStatement(szSQL);
			st.setInt(1, myid);

			rs = st.executeQuery();

			ArrayList<Integer> a = new ArrayList<Integer>();
			while (rs.next()) {
				a.add(rs.getInt("n_tpl"));
			}
			if (a.size() > 0) {
				Random r = new Random();
				int tt = r.nextInt(a.size() + 1);
				if (tt >= a.size())
					i = -1;
				else
					i = a.get(tt);
			}
		} catch (Exception ex) {
			i = -1;
			Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null,
					ex);
		} finally {
			closeConnection(co, st, rs);
		}
		return i;
	}

	public ArrayList<String> getDefinition() {
		return variable;
	}

	/**
	 * Delete a question from the database
	 * 
	 * @param id
	 *            : identifier of the question
	 * @return id deleted question, if everything go fine -1, otherwise
	 * @throws java.lang.Exception
	 */
	public int deleteCategory(int id) throws SQLException, NamingException {
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement stDelete = null;

		int iRes = 0;
		try {
			co = initConnection();

			stDelete = co.prepareStatement("DELETE FROM category WHERE id = ?");
			stDelete.setInt(1, id);
			iRes = stDelete.executeUpdate();

		} catch (SQLException ex) {
			StringBuilder error = new StringBuilder();
			error.append("ERROR: There are a problem deleting the question<br/>\n");
			error.append("SQLState: ").append(ex.getSQLState()).append("<br/>");
			error.append("Message: ").append(ex.getMessage()).append("<br/>");
			error.append("Vendor: ").append(ex.getErrorCode()).append("<br/>");
			iRes = -1;
		} finally {
			closeConnection(co, stDelete, rs);
		}

		return iRes;

	}

	public Category getCategory(int id) throws SQLException, NamingException {
		Category result = null;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		String szSQL;

		try {
			szSQL = "SELECT id,grade,topic FROM category WHERE id = ?";
			co = initConnection();
			st = co.prepareStatement(szSQL);
			st.setInt(1, id);
			rs = st.executeQuery();

			while (rs.next()) {
				result = new Category();
				result.setId(rs.getInt("id"));
				result.setGrade(rs.getInt("grade"));
				result.setTopic(rs.getString("topic"));

			}
		} finally {
			closeConnection(co, st, rs);
		}
		return result;

	}

	public List<Category> getCategoryAll() throws SQLException, NamingException {
		ArrayList<Category> result = null;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		String szSQL;

		try {
			szSQL = "SELECT id,grade,topic FROM category ORDER by grade";
			co = initConnection();
			st = co.prepareStatement(szSQL);
			rs = st.executeQuery();

			result = new ArrayList<Category>();
			while (rs.next()) {
				Category c = new Category();
				c.setId(rs.getInt("id"));
				c.setGrade(rs.getInt("grade"));
				c.setTopic(rs.getString("topic"));
				result.add(c);
			}
		} finally {
			closeConnection(co, st, rs);
		}
		return result;
	}

	public static void addCategory(Category category) throws SQLException {
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement stInsert = null;
		try {

			co = initConnection();
			stInsert = co
					.prepareStatement("INSERT INTO category (id,grade,topic) VALUES(null,?,?)");
			stInsert.setInt(1, category.getGrade());
			stInsert.setString(2, category.getTopic());
			if (stInsert.executeUpdate() < 1) {
				throw new SQLException("new category: " + category.getTopic()
						+ " on Primary " + category.getGrade()
						+ " NOT Inserted");
			}

		} catch (Exception ex) {
		} finally {
			closeConnection(co, stInsert, rs);
		}

	}

	public static void updateCategory(Category category) throws SQLException {
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement stUpdate = null;
		try {

			co = initConnection();
			stUpdate = co
					.prepareStatement("UPDATE category SET grade=?, topic=? WHERE id = ?");
			stUpdate.setInt(1, category.getGrade());
			stUpdate.setString(2, category.getTopic());
			stUpdate.setInt(3, category.getId());
			if (stUpdate.executeUpdate() < 1) {
				throw new SQLException("The existing category: "
						+ category.getTopic() + " on Primary "
						+ category.getGrade() + " NOT Updated");
			}

		} catch (Exception ex) {
		} finally {
			closeConnection(co, stUpdate, rs);
		}

	}

	public String getQuestionTyp(String id) throws SQLException,
			NamingException {
		String result = null;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		String szSQL;
		int myid = Integer.valueOf(id);

		try {
			szSQL = "SELECT category FROM question WHERE id = ?";

			co = initConnection();
			st = co.prepareStatement(szSQL);
			st.setInt(1, myid);

			rs = st.executeQuery();
			if (rs.next()) {
				result = rs.getString("category");
			} else { // Control manually modified ID (Cross-scripting)
				if (getSizeDB() == 0) {
					throw new IllegalArgumentException(
							"Our DB is empty. This action cannot be performed!");
				}
				throw new IllegalArgumentException(
						"This question does not exist in our DB");
			}
		} finally {
			closeConnection(co, st, rs);
		}
		return result;
	}

	public ArrayList<String> getQuestionTypAll() throws SQLException,
			NamingException {
		ArrayList<String> result = null;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		String szSQL;
		int myid = Integer.valueOf(id);

		try {
			szSQL = "SELECT distinct category FROM question ORDER by category";
			co = initConnection();
			st = co.prepareStatement(szSQL);
			rs = st.executeQuery();

			result = new ArrayList<String>();
			while (rs.next()) {
				result.add(rs.getString("category"));
			}
		} finally {
			closeConnection(co, st, rs);
		}
		return result;
	}

	public String getQuestion(String id) throws SQLException, NamingException {
		String result = null;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		String szSQL;
		int myid = Integer.valueOf(id);

		try {
			szSQL = "SELECT template FROM question WHERE id = ?";

			co = initConnection();
			st = co.prepareStatement(szSQL);
			st.setInt(1, myid);

			rs = st.executeQuery();
			if (rs.next()) {
				result = rs.getString("template");
			} else { // Control manually modified ID (Cross-scripting)
				if (getSizeDB() == 0) {
					throw new IllegalArgumentException(
							"Our DB is empty. This action cannot be performed!");
				}
				throw new IllegalArgumentException(
						"This question does not exist in our DB");
			}
		} finally {
			closeConnection(co, st, rs);
		}
		return result;
	}

	public String getQuestion(String id, int tpl_id) throws SQLException,
			NamingException {
		String result = null;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		String szSQL;
		int myid = Integer.valueOf(id);

		try {
			szSQL = "SELECT template FROM question_tpl WHERE id = ? and n_tpl=?";

			co = initConnection();
			st = co.prepareStatement(szSQL);
			st.setInt(1, myid);
			st.setInt(2, tpl_id);

			rs = st.executeQuery();
			if (rs.next()) {
				result = rs.getString("template");
			} else { // Control manually modified ID (Cross-scripting)
				if (getSizeDB() == 0) {
					throw new IllegalArgumentException(
							"Our DB is empty. This action cannot be performed!");
				}
				throw new IllegalArgumentException(
						"This question does not exist in our DB");
			}
		} finally {
			closeConnection(co, st, rs);
		}
		return result;
	}

	/**
	 * Get the variable of a Word
	 * 
	 * @param id
	 *            identifier of the question
	 * @return DBManager with matching identifer
	 * @throws java.sql.SQLException
	 * @throws javax.naming.NamingException
	 */
	public DBManager getDefinition(int id) throws SQLException, NamingException {
		DBManager result = null;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		String szSQL;

		try {
			szSQL = "SELECT template, category FROM question WHERE id = ?";

			co = initConnection();
			st = co.prepareStatement(szSQL);
			st.setInt(1, id);

			rs = st.executeQuery();
			if (rs.next()) {
				result = new DBManager();
				result.id = id;
				result.question = rs.getString("template");
			} else { // Control manually modified ID (Cross-scripting)
				if (getSizeDB() == 0) {
					throw new IllegalArgumentException(
							"Our DB is empty. This action cannot be performed!");
				}
				throw new IllegalArgumentException(
						"This question does not exist in our DB");
			}

			szSQL = "SELECT n_var, variable FROM variable_definition WHERE id = ?";
			st = co.prepareStatement(szSQL);
			st.setInt(1, id);
			rs = st.executeQuery();

			result.variable = new ArrayList<String>();
			while (rs.next()) {
				result.variable.add(rs.getString("variable"));
			}

		} finally {
			closeConnection(co, st, rs);
		}
		return result;
	}

	public ArrayList<String> getDefinition(String id) throws SQLException,
			NamingException {
		ArrayList<String> result = null;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		String szSQL;
		int myid = Integer.valueOf(id);

		try {
			szSQL = "SELECT variable FROM variable_definition WHERE id = ?";
			co = initConnection();
			st = co.prepareStatement(szSQL);
			st.setInt(1, myid);
			rs = st.executeQuery();

			result = new ArrayList<String>();
			while (rs.next()) {
				result.add(rs.getString("variable"));
			}
		} finally {
			closeConnection(co, st, rs);
		}
		return result;
	}

	public ArrayList<String> getRelation(String id) throws SQLException,
			NamingException {
		ArrayList<String> result = null;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		String szSQL;
		int myid = Integer.valueOf(id);

		try {
			szSQL = "SELECT relation FROM variable_relation WHERE id = ?";
			co = initConnection();
			st = co.prepareStatement(szSQL);
			st.setInt(1, myid);
			rs = st.executeQuery();

			result = new ArrayList<String>();
			while (rs.next()) {
				result.add(rs.getString("relation"));
			}
		} finally {
			closeConnection(co, st, rs);
		}
		return result;
	}

	public ArrayList<String> getSolution(String id) throws SQLException,
			NamingException {
		ArrayList<String> result = null;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		String szSQL;
		int myid = Integer.valueOf(id);

		try {
			szSQL = "SELECT solution FROM variable_solution WHERE id = ?";
			co = initConnection();
			st = co.prepareStatement(szSQL);
			st.setInt(1, myid);
			rs = st.executeQuery();

			result = new ArrayList<String>();
			while (rs.next()) {
				result.add(rs.getString("solution"));
			}
		} finally {
			closeConnection(co, st, rs);
		}
		return result;
	}

	public ArrayList<String> getTemplate(String id) throws SQLException,
			NamingException {
		ArrayList<String> result = null;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		String szSQL;
		int myid = Integer.valueOf(id);

		try {
			szSQL = "SELECT template FROM question_tpl WHERE id = ?";
			co = initConnection();
			st = co.prepareStatement(szSQL);
			st.setInt(1, myid);
			rs = st.executeQuery();

			result = new ArrayList<String>();
			while (rs.next()) {
				result.add(rs.getString("template"));
			}
		} finally {
			closeConnection(co, st, rs);
		}
		return result;
	}

	/**
	 * Get the number of words contained in the DB
	 * 
	 * @return DB.word.size()
	 * @throws java.sql.SQLException
	 */
	public static int getSizeDB() throws SQLException {
		int iSize = -1;
		Connection co = null;
		ResultSet rs = null;
		Statement st = null;

		try {
			co = initConnection();
			st = co.createStatement();
			rs = st.executeQuery("SELECT COUNT(*) FROM testdata");
			rs.first();
			iSize = rs.getInt("COUNT(*)");

		} catch (Exception ex) {
		} finally {
			closeConnection(co, st, rs);
		}

		return iSize;
	}

	public static String doAlgCalculation(String expression, boolean decimal)
			throws SQLException {
		String result = " ";
		Connection co = null;
		ResultSet rs = null;
		Statement st = null;
		expression = expression.replaceAll("\\s+", "");
		expression = AlgExpression(expression);
		String query = "select " + expression + " as answer";
		try {
			co = initConnection();
			st = co.createStatement();
			rs = st.executeQuery(query);
			rs.first();
			result = rs.getString(1);

		} catch (Exception ex) {
			return expression;
		} finally {
			closeConnection(co, st, rs);
		}

		return result;
	}

	public static String doCalculation(String expression) throws SQLException {
		int result = -1;
		Connection co = null;
		ResultSet rs = null;
		Statement st = null;
		expression = expression.replaceAll("\\s+", "");
		String query = "select " + expression + " as answer";

		try {
			co = initConnection();
			st = co.createStatement();
			rs = st.executeQuery(query);
			rs.first();
			result = rs.getInt(1);

		} catch (Exception ex) {
			return expression;
		} finally {
			closeConnection(co, st, rs);
		}

		return String.valueOf(result);
	}

	public static String doCalculation(String expression, boolean decimal)
			throws SQLException {
		float result = -1;
		Connection co = null;
		ResultSet rs = null;
		Statement st = null;
		expression = expression.replaceAll("\\s+", "");
		String query = "select " + expression + " as answer";
		try {
			co = initConnection();
			st = co.createStatement();
			rs = st.executeQuery(query);
			rs.first();
			result = rs.getFloat(1);

		} catch (Exception ex) {
			return expression;
		} finally {
			closeConnection(co, st, rs);
		}

		return String.valueOf(result);
	}

	public int getId() {
		return id;
	}

	public String getQuestion() {
		return question;
	}

	// 0 -not person 1-she 2-he
	public static int isHeShe(String nam) throws SQLException, NamingException {
		Connection co = null;
		PreparedStatement stSelect = null;
		String szSQL;
		try {
			co = initConnection();
			if (nam == null || nam.length() < 1) {
				return 0;
			} else {
				stSelect = co
						.prepareStatement("SELECT type2 FROM phrase_dictionary WHERE phrasename = ?");
				stSelect.setString(1, nam);
			}

			ResultSet rs = stSelect.executeQuery();
			if (rs.next()) {
				String typnam = TypNumNam(rs.getInt(1));
				if (typnam.contains("Girl") || typnam.contains("Female")) {
					return 1;
				} else if (typnam.contains("Boy") || typnam.contains("Male")) {
					return 2;
				} else
					return 0;
			}
		} catch (SQLException ex) {
			// InOut.setStatus(ex.getErrorCode(),
			// "ERROR: There are problems inserting the question<br/>\n" +
			// ex.toString());
			return 0;
		} finally {
			closeConnection(co, stSelect, null);
		}
		return 0;
	}

	public static int TypNamNum(String typnam) throws SQLException {
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		int i = 0;
		try {
			co = initConnection();
			st = co.prepareStatement("SELECT id FROM variable_types WHERE typename=?");
			st.setString(1, typnam);
			rs = st.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (Exception ex) {
		} finally {
			closeConnection(co, st, rs);
		}
		return 0;
	}

	public static String TypNumNam(int typnum) throws SQLException {
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		int i = 0;
		try {
			co = initConnection();
			st = co.prepareStatement("SELECT typename FROM variable_types WHERE id=?");
			st.setInt(1, typnum);
			rs = st.executeQuery();
			if (rs.next()) {
				return rs.getString(1);
			}
		} catch (Exception ex) {
		} finally {
			closeConnection(co, st, rs);
		}
		return "Not Defined";
	}

	public static void addVarType(String typename, String suptype)
			throws SQLException {
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement stInsert = null;
		int i = 0;
		try {
			if (!isTypeExist(typename)) {
				co = initConnection();
				stInsert = co
						.prepareStatement("INSERT INTO variable_types (id,typename, supid, supid1,supid2) VALUES(null,?,?,0,0)");
				stInsert.setString(1, typename);
				stInsert.setInt(2, TypNamNum(suptype));
				if (stInsert.executeUpdate() < 1) {
					throw new SQLException("new type: " + typename
							+ " NOT Inserted");
				}
			}
		} catch (Exception ex) {
		} finally {
			closeConnection(co, stInsert, rs);
		}

	}

	public static boolean isTypeExist(String typename) throws SQLException {
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		ArrayList<Integer> a = new ArrayList<Integer>();

		try {
			co = initConnection();
			st = co.prepareStatement("SELECT id FROM variable_types where typename=?");
			st.setString(1, typename);
			rs = st.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (Exception ex) {
			Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null,
					ex);
		} finally {
			closeConnection(co, st, rs);
		}
		return false;
	}

	public static boolean DeleteType(String typenum) throws SQLException {
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		ArrayList<Integer> a = new ArrayList<Integer>();

		try {
			co = initConnection();
			st = co.prepareStatement("Update Phrase_dictionary set type1=0 where type1=?");
			st.setInt(1, Integer.valueOf(typenum));
			st.executeUpdate();
			st = co.prepareStatement("Update Phrase_dictionary set type2=0 where type2=?");
			st.setInt(1, Integer.valueOf(typenum));
			st.executeUpdate();
			st = co.prepareStatement("Update Phrase_dictionary set type3=0 where type3=?");
			st.setInt(1, Integer.valueOf(typenum));
			st.executeUpdate();
			st = co.prepareStatement("Update Phrase_dictionary set type4=0 where type4=?");
			st.setInt(1, Integer.valueOf(typenum));
			st.executeUpdate();

			co = initConnection();
			st = co.prepareStatement("Delete FROM variable_types where typename=?");
			st.setString(1, TypNumNam(Integer.valueOf(typenum)));
			st.executeUpdate();

			return true;
		} catch (Exception ex) {
			Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null,
					ex);
		} finally {
			closeConnection(co, st, rs);
		}
		return false;
	}

	public static String[] getVarTypes(int sub) throws SQLException {
		Connection co = null;
		ResultSet rs = null;
		Statement st = null;
		PreparedStatement stp = null;
		String VarTypes[] = new String[10];
		int i = 0;
		try {
			co = initConnection();
			st = co.createStatement();
			if (sub == 0) {
				rs = st.executeQuery("SELECT typename,id,supid,supid1,supid2 FROM variable_types where supid=0;");
			} else if (sub == 9999) {
				rs = st.executeQuery("SELECT typename,id,supid,supid1,supid2 FROM variable_types where supid!=0;");
			} else {
				rs = st.executeQuery("SELECT typename,id,supid,supid1,supid2 FROM variable_types where supid!=0;");
				stp = co.prepareStatement("SELECT typename,id,supid,supid1,supid2 FROM variable_types where supid=? or supid1=? or supid2=?;");
				stp.setInt(1, sub);
				stp.setInt(2, sub);
				stp.setInt(3, sub);
				rs = stp.executeQuery();
			}
			while (rs.next()) {
				VarTypes[i] = rs.getString(1);

				i++;
			}

		} catch (Exception ex) {
		} finally {
			closeConnection(co, st, rs);
		}

		return VarTypes;
	}

	public static ArrayList<Integer> isPhraseExist(String phs)
			throws SQLException {
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;
		ArrayList<Integer> a = new ArrayList<Integer>();
		phs = (new NameManager().dbNames(phs));
		try {
			co = initConnection();
			st = co.prepareStatement("SELECT id,type1,type2,type3,type4 FROM phrase_dictionary WHERE LOWER(phrasename)=LOWER(?)");
			st.setString(1, phs);
			rs = st.executeQuery();
			if (rs.next()) {
				a.add(rs.getInt(2));
				a.add(rs.getInt(3));
				a.add(rs.getInt(4));
				a.add(rs.getInt(5));
				return a;
			}
			/*
			 * else if(!WordManager.isPluralForm(phs)){ //may be it is singular
			 * norn, check if its plural exists or not st = co.prepareStatement(
			 * "SELECT id,type1,type2,type3,type4 FROM phrase_dictionary WHERE LOWER(phrasename)=LOWER(?)"
			 * ); st.setString(1, WordManager.toPluralForm(phs)); rs =
			 * st.executeQuery(); if (rs.next()){ a.add(rs.getInt(2));
			 * a.add(rs.getInt(3)); a.add(rs.getInt(4)); a.add(rs.getInt(5));
			 * return a;} }
			 */

		} catch (Exception ex) {
			Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null,
					ex);
		} finally {
			closeConnection(co, st, rs);
		}
		return a;
	}

	/**
	 * Generate a random question id (accessing to DB)
	 * 
	 * @return random entry identifier
	 * @throws java.sql.SQLException
	 */
	public static ArrayList<String> getRandomName(int ty, int num)
			throws SQLException {
		ArrayList<String> result = new ArrayList<String>();
		int count;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;

		try {
			co = initConnection();
			st = co.prepareStatement("SELECT phrasename FROM phrase_dictionary WHERE type1 = ? or type2 = ? "
					+ "or type3=? or type4=?");
			st.setInt(1, ty);
			st.setInt(2, ty);
			st.setInt(3, ty);
			st.setInt(4, ty);
			rs = st.executeQuery();
			ArrayList<String> a = new ArrayList<String>();
			while (rs.next()) {
				a.add(rs.getString("phrasename"));
			}
			if (a.size() < num) {
				throw Exception;
			}
			while (result.size() < num) {
				Random r = new Random();
				String name = a.get(r.nextInt(a.size()));
				if (!result.contains(name)) {
					result.add(name);
				}
			}
		} catch (Exception ex) {
			result.clear();
			for (int i = 0; i < num; i++) {
				result.add("");
			}
			Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null,
					ex);
		} finally {
			closeConnection(co, st, rs);
		}
		return result;
	}

	/**
	 * Generate a random question id (accessing to DB)
	 * 
	 * @return random entry identifier
	 * @throws java.sql.SQLException
	 */
	public static String getRandomName(int ty) throws SQLException {
		String result = new String();
		int count;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;

		try {
			co = initConnection();
			st = co.prepareStatement("SELECT phrasename FROM phrase_dictionary WHERE type1 = ? or type2 = ? "
					+ "or type3=? or type4=?");
			st.setInt(1, ty);
			st.setInt(2, ty);
			st.setInt(3, ty);
			st.setInt(4, ty);
			rs = st.executeQuery();
			ArrayList<String> a = new ArrayList<String>();
			while (rs.next()) {
				a.add(rs.getString("phrasename"));
			}
			if (a.size() < 1) {
				throw Exception;
			}
			Random r = new Random();
			result = a.get(r.nextInt(a.size()));
		} catch (Exception ex) {
			Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null,
					ex);
			return "";
		} finally {
			closeConnection(co, st, rs);
		}
		return result;
	}

	/**
	 * Generate a random question id (accessing to DB)
	 * 
	 * @return random entry identifier
	 * @throws java.sql.SQLException
	 */
	public static int getDictSize(int ty) throws SQLException {
		String result = new String();
		int count;
		Connection co = null;
		ResultSet rs = null;
		PreparedStatement st = null;

		try {
			co = initConnection();
			st = co.prepareStatement("SELECT count(*) FROM phrase_dictionary WHERE type1 = ? or type2 = ? "
					+ "or type3=? or type4=?");
			st.setInt(1, ty);
			st.setInt(2, ty);
			st.setInt(3, ty);
			st.setInt(4, ty);
			rs = st.executeQuery();
			ArrayList<String> a = new ArrayList<String>();
			if (rs.next()) {
				return rs.getInt("count(*)");
			}
		} catch (Exception ex) {
			Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null,
					ex);
			return 0;
		} finally {
			closeConnection(co, st, rs);
		}
		return 0;
	}

	public static Connection initConnection() throws NamingException,
			SQLException {
		Context initCtx = new InitialContext();
		Context envCtx = (Context) initCtx.lookup("java:comp/env");
		DataSource ds = (DataSource) envCtx.lookup("jdbc/QASDB");
		return ds.getConnection();
	}

	/**
	 * Try to close cleanly a DB connection releasing Statement and ResulSet
	 * resources.
	 * 
	 * @param co
	 *            : connection to close
	 * @param st
	 *            : statement to close
	 * @param rs
	 *            : resultset to close
	 * @throws java.sql.SQLException
	 */
	public static void closeConnection(Connection co, Statement st, ResultSet rs)
			throws SQLException {
		try {
			if (rs != null) {
				rs.close();
			}
		} finally {
			try {
				if (st != null) {
					st.close();
				}
			} finally {
				if (!co.isClosed()) {
					co.close();
				}
			}
		}
	}

	/*
	 * known issue: (10+1)+3x
	 */
	public static String AlgExpression2(String str) {
		// hj: SQRT/POW/EXP functions, make sure uppercase it.
		str = str.replaceAll("sqrt", "SQRT");
		str = str.replaceAll("pow", "POW");

		// hj check for xyz terms, substitute with single term
		String tmp = str + " ";
		String substr = "aloi";// for xy xyz etc...
		ArrayList<String> tosub = new ArrayList<String>();
		Pattern pattern2 = Pattern
				.compile("[^a-z][0-9]*[a-z][a-z][a-z]*[^a-z]");

		Matcher matcher2 = pattern2.matcher(tmp);
		int k = 0;
		while (matcher2.find()) {
			tosub.add(tmp.substring(matcher2.start() + 1, matcher2.end() - 1));
			str = str.replace(tosub.get(k), String.valueOf(substr.charAt(k)));
			k++;
		}
		// ...end

		tmp = str + " ";
		Pattern pattern = Pattern.compile("[^a-z][a-z][^a-z]");

		Matcher matcher = pattern.matcher(tmp);
		if (!matcher.find()) {
			return str;
		}

		char[] sss = new char[10];
		int j = 0;
		boolean endwithvar = false;
		for (int i = 0; i < str.length(); i++) {
			if (str.charAt(i) <= 'z' && str.charAt(i) >= 'a') {
				if (i > 0 && str.charAt(i - 1) == '(') {
					return "-1"; // I don't mess with this
				}
				sss[j] = str.charAt(i);
				j++;
				if (i == str.length() - 1) {
					endwithvar = true;
				}
			}
		}
		if (!endwithvar) {
			j++;
		}

		if (j == 0) {
			return str;
		}
		String ss[] = str.split("[a-z]", 0);
		String query = "CONCAT(";
		for (int i = 0; i < j; i++) {

			String signfact = "";
			char ssssign = ' ';
			try {

				if (ss[i].length() > 0) {
					if ((ss[i].charAt(ss[i].length() - 1) == '+'
							|| ss[i].charAt(ss[i].length() - 1) == '-'
							|| ss[i].charAt(ss[i].length() - 1) == '*' || ss[i]
								.charAt(ss[i].length() - 1) == '/')) {
						ssssign = ss[i].charAt(ss[i].length() - 1);
						ss[i] = ss[i].substring(0, ss[i].length() - 1);
					}
				}
				if (ss[i].length() > 0) {

					if (i > 0 && Integer.valueOf(doCalculation((ss[i]))) >= 0) {
						signfact = "+";
					}
				}

				query = query + "'" + signfact + "',";
				if (ss[i].length() > 0) {
					query = query + ss[i] + ",";
				}
				if (ssssign == ' ') {
					query = query + "'" + sss[i] + "'";
				} else {
					query = query + "'" + ssssign + sss[i] + "'";
				}
				if (i < j - 1) {
					query = query + ",";
				}
			} catch (SQLException ex) {
				Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE,
						null, ex);
			}

		}
		query = query + ")";
		for (int i = 0; i < tosub.size(); i++) {
			query = query.replace(String.valueOf(substr.charAt(i)),
					tosub.get(i));
		}
		return query;
	}

	public static String AlgExpression(String str) {
		// hj: SQRT/POW/EXP functions, make sure uppercase it.
		str = str.replaceAll("sqrt", "SQRT");
		str = str.replaceAll("pow", "POW");

		// hj check for xyz terms, substitute with single term
		String tmp = str + " ";
		String substr = "aloi";// for xy xyz etc...
		ArrayList<String> tosub = new ArrayList<String>();
		Pattern pattern2 = Pattern
				.compile("[^a-z][0-9]*[a-z][a-z][a-z]*[^a-z]");

		Matcher matcher2 = pattern2.matcher(tmp);
		int k = 0;
		while (matcher2.find()) {
			tosub.add(tmp.substring(matcher2.start() + 1, matcher2.end() - 1));
			str = str.replace(tosub.get(k), String.valueOf(substr.charAt(k)));
			k++;
		}
		// ...end

		tmp = str + " ";
		Pattern pattern = Pattern.compile("[^a-z][a-z][^a-z]");

		Matcher matcher = pattern.matcher(tmp);
		if (!matcher.find()) {
			return str;
		}

		char[] sss = new char[10];
		int j = 0;
		boolean endwithvar = false;
		for (int i = 0; i < str.length(); i++) {
			if (str.charAt(i) <= 'z' && str.charAt(i) >= 'a') {
				if (i > 0 && str.charAt(i - 1) == '(') {
					return "-1"; // I don't mess with this
				}
				sss[j] = str.charAt(i);
				j++;
				if (i == str.length() - 1) {
					endwithvar = true;
				}
			}
		}
		if (!endwithvar) {
			j++;
		}

		if (j == 0) {
			return str;
		}
		String ss[] = str.split("[a-z]", 0);
		String query = "CONCAT(";
		for (int i = 0; i < j; i++) {

			String signfact = "";
			char ssssign = ' ';
			try {

				if (ss[i].length() > 0) {
					if ((ss[i].charAt(ss[i].length() - 1) == '+'
							|| ss[i].charAt(ss[i].length() - 1) == '-'
							|| ss[i].charAt(ss[i].length() - 1) == '*' || ss[i]
								.charAt(ss[i].length() - 1) == '/')) {
						ssssign = ss[i].charAt(ss[i].length() - 1);
						ss[i] = ss[i].substring(0, ss[i].length() - 1);
					}
				}
				if (ss[i].length() > 0) {

					if (i > 0 && Integer.valueOf(doCalculation((ss[i]))) >= 0) {
						signfact = "+";
					}
				}

				query = query + "'" + signfact + "',";
				if (ss[i].length() > 0) {
					query = query + ss[i] + ",";
				}
				if (ssssign == ' ') {
					query = query + "'" + sss[i] + "'";
				} else {
					query = query + "'" + ssssign + sss[i] + "'";
				}
				if (i < j - 1) {
					query = query + ",";
				}
			} catch (SQLException ex) {
				Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE,
						null, ex);
			}

		}
		query = query + ")";
		for (int i = 0; i < tosub.size(); i++) {
			query = query.replace(String.valueOf(substr.charAt(i)),
					tosub.get(i));
		}
		return query;
	}
}
